package edu.depaul.se.courseregistration.sessionbean;

import javax.ejb.EJB;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;

/**
 *
 * @author Group2
 */
@Stateless
@LocalBean

public class InitTablesSession  {

    @EJB
    InitTableEJB initTableEJB;
    
    public  void initDB() {

        String[] stmt = {

            //populate Person table  
            // Student
            "insert into person values(101,'Student',null,'daveabrock@gmail.com','Dave','Brock','Chicago','IL','1 W State St.','60613','Computer Science')",
            "insert into person values(102,'Student',null,'steve@gmail.com','Steve','Sax','Oak Park','IL','123 Main St.','60665','Political Science')",
            // Registrar
            "insert into person values(103,'Registrar','Academic Affairs','steveyoung@gmail.com','Steve','Young','Berwyn','IL','456 Main St.','60665',null)",
            // Professor
            "insert into person values(104,'Professor','Software Engineering','kenyu@depaul.edu','Ken','Yu','Chicago','IL','999 Main St.','60665',null)",
            "insert into person values(105,'Professor','Philosophy','kyle@gmail.com','Kyle','Kay','Chicago','IL','111 W State St.','60665',null)",
            // DBAdmin
            "insert into person values(106,'DBAdmin','Information Technology','shelly@gmail.com','Shelly','Sven','Chicago','IL','333 W State St.','60666',null)",
            
            //populate College table
            "insert into college values(1,'College of Computing & Digital Media','SCTI')",
            "insert into college values(2,'College of Commerce','SC')",
            "insert into college values(3,'College of Music','SM')",
            
            //populate SUBJECT table
            //College1 - CDM 
            "insert into subject values(100, 'SE', 'Software Enginerring', 1)",
            "insert into subject values(101, 'IS', 'Information System', 1)",
            "insert into subject values(102, 'PM', 'Project Management', 1)",
            //College 2 - SC
            "insert into subject values(200, 'FIN', 'Finance', 2)",
            "insert into subject values(201, 'ACC', 'Accountancy', 2)",
            "insert into subject values(202, 'MGT', 'Management', 2)",
            //College 3 - SM
            "insert into subject values(300, 'MED', 'Music Education', 3)",
           
            //populate COURSE Tables
            //Subject 100
            "insert into course values(554,'This course will focus on object-oriented component architectures for enterprise applications. "
                + "Topics include: Enterprise Java Beans (EJB), Java Naming and Directory Interfaces (JNDI), "
                + "Java Mail and Messaging Services','SE 450 or SE 452','Enterprise component Arch',2,'Winter 2011-2012','2012',100)",
            "insert into course values(430,'Object-oriented modeling techniques for analysis and design.','CSC212 or CSC262','OBJECT ORIENTED MODELING',3,'Spring 2011-2012','2012',100)",
            
            //Subject 101
             "insert into course values(421,'Course focus is on both traditional and object oriented systems analysis, "
                + "with an emphasis upon developing competency in a wide range of modeling techniques','Completion of Prerequisite Phase','SYSTEMS ANALYSIS'"
                + ",3,'Spring 2011-2012','2012',101)",
            "insert into course values(422 ,'Course focus is on both traditional and object oriented systems design.','IS 421, Systems Analysis','SYSTEM DESIGN, IMPLEMENTATION, AND MAINTENANCE'"
                + ",4,'Summer 2011-2012','2012',101)",
            //Subject102
            "insert into course values(570,'This course is targeted towards information systems professionals who are involved in the "
                + "planning and implementation of large scale, cross-functional enterprise systems.','IS 430 or PM 430 or SE 477 or completion of foundation or core phase',"
                + "'ENTERPRISE SYSTEM IMPLEMENTATION',3,'Spring 2011-2012','2012',102)",

            
            //populate COURSE_SECTION table
            //insert section for 554, 430, 421, 422, 570 courses.
            "insert into course_section values(41771, 'LOOP','TBD', 1, 'Mon 5:00-5:00pm','rm801', '3/24-6/1',2500, 554, null)",
            "insert into course_section values(41772, 'LOOP','TBD', 1, 'Mon 5:00-5:00pm', 'ONLINE','3/24-6/1',2500, 554, null)",
            "insert into course_section values(41773, 'dpc','TBD', 1,  'Tue 5:00-5:00pm', 'rm801','1/1-3/10',2500, 430, null)",
            "insert into course_section values(41774, 'dpc','TBD', 1,  'Tue 5:00-5:00pm', 'ONLINE','1/1-3/10',2500, 430, null)",
            "insert into course_section values(41775, 'dpc','TBD', 1,  'wed 5:00-5:00pm','rm801','TBD',2500, 421, null)",
            "insert into course_section values(41776, 'dpc','TBD', 1,  'Wed 5:00-5:00pm','ONLINE','TBD',2500, 421, null)",
            "insert into course_section values(41777, 'dpc','TBD', 1,  'Thu 5:00-5:00pm','rm801','TBD',2500, 422, null)",
            "insert into course_section values(41778, 'dpc','TBD', 1,  'Thu 5:00-5:00pm','ONLINE','TBD',2500, 422, null)",
            "insert into course_section values(41779, 'dpc','TBD', 1,  'Mon 5:00-5:00pm','rm801','TBD',2500, 570, null)",
            "insert into course_section values(41780, 'dpc','TBD', 1,  'Mon 5:00-5:00pm','ONLINE','TBD',2500, 570, null)",

        };

          initTableEJB.initDB(stmt);
    }

    // delete all data in tables -> result: empty tables
    public  void clearDB() {
        String[] stmt = {
            "delete from person",
            "delete from schedule",
            "delete from course_section",
            "delete from course",
            "delete from subject",
            "delete from college"
        };
        initTableEJB.clearDB(stmt);
    }   
    
    // add some static variables here 
    // may need to update here if initDB() changes.
    public static int NUM_OF_ROWS_IN_COLLEGE_TABLE = 3;
    public static int NUM_OF_ROW_IN_PERSON_TABLE = 6;
    public static int NUM_OF_ROWS_IN_SUBJECT_TABLE = 7;
    public static int NUM_OF_ROWS_IN_SECTIONS_TABLE = 10;
    public static int NUM_OF_ROWS_IN_COURSE_TABLE = 5;
    public static int NUM_OF_ROWS_IN_SUBJECT_IS_TABLE = 2;
    public static int NUM_OF_ROWS_IN_SUBJECT_SE_TABLE = 2;
    public static int NUM_OF_ROWS_IN_COLLEGE_1_TABLE = 3;
    public static int NUM_OF_ROWS_IN_COLLEGE_2_TABLE = 3;
    public static int NUM_OF_ROWS_IN_COLLEGE_3_TABLE = 1;




}

